package com.wanmait.spacesys.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.wanmait.spacesys.util.DbHelper;
import com.wanmait.spacesys.vo.Collection;
import com.wanmait.spacesys.vo.Post;
import com.wanmait.spacesys.vo.Userinfo;

/**
 * 数据持久化层:用户个人中心我的收藏
 * @author 28298
 *
 */
public class MyCollectionDAO {
	//单一实列
    private MyCollectionDAO() {}
    private static MyCollectionDAO myCollectionDAO=new MyCollectionDAO();
    public static MyCollectionDAO getMyCollectionDAO() {
    	return myCollectionDAO;
    }
    
//    //用户收藏:统计分页总数量
//    public int getCollectionCount(Integer userinfoId) {
//    	int collectionCount=0;
//    	Connection connection=DbHelper.getConnection();
//  		PreparedStatement preparedStatement=null;
//  		ResultSet resultSet=null;
//    	String sql="SELECT COUNT(collection.userinfo_id) FROM collection LEFT JOIN post ON collection.post_id=post.id LEFT JOIN userinfo ON collection.userinfo_id =userinfo.id WHERE userinfo.id=?";
//    	try {
//			preparedStatement=connection.prepareStatement(sql);
//			preparedStatement.setInt(1, userinfoId);
//			resultSet=preparedStatement.executeQuery();
//			if(resultSet.next()) {
//				collectionCount=resultSet.getInt(1);
//			}
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
//    	return collectionCount;
//    }

  	
  	//通过用户id统计收藏总数
  	public int getCount(Integer userinfoId) {
		int count = 0;
		String sql = "SELECT COUNT(collection.userinfo_id) FROM collection LEFT JOIN post ON collection.post_id=post.id WHERE collection.userinfo_id=? AND post.enable=1";
		Connection connection = DbHelper.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, userinfoId);
			resultSet = preparedStatement.executeQuery();
			if(resultSet.next()) {
				count = resultSet.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DbHelper.close(resultSet,preparedStatement,connection);
		}
		return count;
		
	}
  //根据用户id查询用户所有的收藏
  	public List<Collection> findAllById(Integer userinfoId,int pageNum){
  		List<Collection> collections=new ArrayList<Collection>();
  		Connection connection=DbHelper.getConnection();
  		PreparedStatement preparedStatement=null;
  		ResultSet resultSet=null;
  	
  		String sql= "SELECT collection.id,collection.post_id,collection.userinfo_id,post.title,post.content,post.pub_time,username,post.enable FROM collection LEFT JOIN post ON collection.post_id=post.id LEFT JOIN userinfo ON collection.userinfo_id =userinfo.id WHERE userinfo.id=? and post.enable=1 "
  				   + "limit "+(pageNum-1)*2+",2";
  		try {
			preparedStatement=connection.prepareStatement(sql);
			preparedStatement.setInt(1, userinfoId);
			resultSet=preparedStatement.executeQuery();
			while(resultSet.next()) {
				Collection collection=new Collection();
				collection.setId(resultSet.getInt("id"));
	  			Post post=new Post();
	  			post.setId(resultSet.getInt("post_id"));
	  			post.setTitle(resultSet.getString("title"));
	  			post.setContent(resultSet.getString("content"));
	  			post.setPubTime(resultSet.getTimestamp("pub_time"));
	  			post.setEnable(resultSet.getBoolean("enable"));
	  			collection.setPost(post);
	  			Userinfo userinfo=new Userinfo();
	  			userinfo.setId(resultSet.getInt("userinfo_id"));
	  			userinfo.setUsername(resultSet.getString("username"));
	  			collection.setUserinfo(userinfo);
				collections.add(collection);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
  		finally {
			DbHelper.close(resultSet,preparedStatement,connection);
		}
  		return collections;
  	}
  	
  	
  	
  	//用户个人中心收藏内容详细内容显示
  	public Collection contentDisplayFindAll(Integer userinfoId,Integer collectionId){
  		
  		Connection connection=DbHelper.getConnection();
  		PreparedStatement preparedStatement=null;
  		ResultSet resultSet=null;
  		Collection collection=null;
  		String sql="SELECT collection.id,collection.post_id,collection.userinfo_id,post.title,post.content,post.pub_time,username FROM collection LEFT JOIN post ON collection.post_id=post.id LEFT JOIN userinfo ON collection.userinfo_id =userinfo.id WHERE userinfo.id=? and post.enable=1 and collection.id=?";
  		try {
			preparedStatement=connection.prepareStatement(sql);
			preparedStatement.setInt(1, userinfoId);
			preparedStatement.setInt(2, collectionId);
			resultSet=preparedStatement.executeQuery();
			while(resultSet.next()) {
				collection=new Collection();
				collection.setId(resultSet.getInt("id"));
	  			Post post=new Post();
	  			post.setId(resultSet.getInt("post_id"));
	  			post.setTitle(resultSet.getString("title"));
	  			post.setContent(resultSet.getString("content"));
	  			post.setPubTime(resultSet.getTimestamp("pub_time"));
	  			collection.setPost(post);
	  			Userinfo userinfo=new Userinfo();
	  			userinfo.setId(resultSet.getInt("userinfo_id"));
	  			userinfo.setUsername(resultSet.getString("username"));
	  			collection.setUserinfo(userinfo);
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
  		finally {
			DbHelper.close(resultSet,preparedStatement,connection);
		}
  		return collection;
  	}
  	

  	
  	//删除收藏
  	public void delete(Integer id) {
  		String sql="delete FROM collection WHERE id=?";
  		DbHelper.update(sql, id);
  	}
  	
  	public static void main(String[] args) {
//		int count=MyCollectionDAO.getMyCollectionDAO().getCount(2);
//		System.out.println(count);
  		Collection collections=MyCollectionDAO.getMyCollectionDAO().contentDisplayFindAll(1, 1);
  		System.out.println(collections.getPost().getContent());
  		
  		//MyCollectionDAO.getMyCollectionDAO().delete(7);
	}

	
}
